<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>query_script_variables: common_schema documentation</title>
	<meta name="description" content="query_script_variables: common_schema" />
	<meta name="keywords" content="query_script_variables: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="query_script_variables.html">query_script_variables</a></h2>	

<p>
QueryScript Variables: creation, assignment, expansion & cleanup
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>
var $v1, $v2, $v3;
set $v1 := 1;
var $v_pi := 3.14;
var $table_name := 'rental';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = $table_name;
ALTER TABLE sakila.:$table_name ENGINE=InnoDB; 
CREATE TABLE test.tmp_:${table_name}_tbl (n INT) ENGINE=InnoDB;
INSERT INTO test.tmp_:${table_name}_tbl SELECT n FROM numbers;
SELECT $rowcount, $found_rows;
</pre></blockquote>
</p>

<h3>DESCRIPTION</h3>
<p> 
In addition to supporting MySQL's 
<a href="http://dev.mysql.com/doc/refman/5.1/en/user-variables.html">user defined variables</a>,
QueryScript introduces script local variables, with controlled creation and cleanup, and
with supported in-place expansion.
</p>
<h4>Declaration, usage and cleanup</h4>
<p>
The following code declares, sets and reads local variables:
<blockquote><pre>var $x := 3; 
while ($x > 0)
{
  var $y := CONCAT('Value of $x is: ', $x);
  SELECT $y AS msg;
  set $x := $x - 1;
}
</pre></blockquote>
</p>
<p>
Multiple variables can be declared with a single <strong>var</strong> statement:
<blockquote><pre>var $x, $y, $z;
</pre></blockquote>
It is possible to declare and assign a variable within the <strong>var</strong> statement as follows:
<blockquote><pre>var $x := 'declared!';
</pre></blockquote>
However this is limited to a single variable. It is not possible to declare and assign multiple variables from within
the same <strong>var</strong> statement. Multiple <strong>var</strong> statements would be required for that - one per variable.
</p>
<p>
QueryScript variables behave much like a <i>user defined variable</i>. 
They can be assigned to, read from, used within a query:
<blockquote><pre>var $x := 3;
SELECT $x, $x + 1 AS next_value;
SET $x := POW($x, 2);
SET @msg := CONCAT('value is ', $x);
</pre></blockquote>
</p>
<p>
However, the following differentiates it from MySQL's user defined variables:
<ul>
	<li>Variables must be declared by the <a href="query_script_var.html">var</a> statement.</li>
	<li>At the point of declaration, they are known to be <strong>NULL</strong> (unless assigned with a value at point of declaration)</li>
	<li>Local variables are only recognized within their scope (see following).</li>
	<li>Once a variable's scope terminates, the variable is reset to <strong>NULL</strong>.
	  In the above <strong>while</strong> loop example, <strong>$y</strong> is being reset to null at the end of each
	  loop iteration.</li>
</ul>
MySQL's user defined variables, in contrast, retain their value throughout the session, or until they are
assigned a new one.
</p>
<p>
Variables can be declared at any point; they do not necessarily have to be declared at
the beginning of a block or script.
</p>
<p>
A <a href="query_script_foreach.html">foreach</a> loop also declares variables, where the <i>var</i> statement
is not required.
</p>
<p>
Variable names are case-sensitive.
</p>
<p>
Note: current implementation uses MySQL's user defined variables, using variable names which are
unique within the script and the session in which they are declared.
</p>

<h4>Visibility & scope</h4>
<p>
A variable is only visible in the scope in which it is declared. In the above example,
<strong>$x</strong> is recognized throughout the script, but <strong>$y</strong> may
only be accessed from within the loop's block.
</p>
<p>
One may use the above facts to force both cleanup and hiding of variables, by creating 
sub-blocks of code:
<blockquote><pre>{
  var $x;
  set $x := 3;
}
-- $x is known to be cleared at this point, and will
-- not be recognized from this point and on.
{
  var $y := 'abc';
} 
-- $y is known to be cleared at this point, and will
-- not be recognized from this point and on.
</pre></blockquote>
</p>
<p>
One may declare two variables of the same name, as long as they are invisible to each other.
In other words, they must be in non-overlapping scopes. For example, the following is valid:
<blockquote><pre>{
  var $a := 3;
  var $b := 4;
}
var $a := 5;
{
  var $b := '6';
} 
</pre></blockquote>
The second declaration point of <strong>$a</strong> appears after the first one went out of scope,
which makes this a valid declaration. The same goes for <strong>$b</strong>.
</p>

<a name="expansion"></a>
<h4>Expansion</h4>
<p>
A variable may be <i>expanded</i> in-place. <i>Expansion</i> means the variable is replaced with the constant value it holds.
Expansion allows the programmer to use variables where variables are not allowed. To illustrate, we must first look at the basics.
</p>
<p>
The value held by the local variable is interpreted as text, and is seamlessly integrated with
the surrounding statement or expression.
</p>

<p>
Expansion syntax:
<blockquote><pre>
var $foo := 3;
SELECT $foo, :$foo, :${foo};

var $bar := 'Population &gt; 1000000';
SELECT * FROM world.Country WHERE :$bar;
</pre></blockquote>
</p>

<p>
Consider the following code:
<blockquote><pre>
var $x := 3;
SELECT $x, :$x;

+--------------------+---+
| @__qs_local_var_16 | 3 |
+--------------------+---+
|                  3 | 3 |
+--------------------+---+
</pre></blockquote>
The above is somewhat delicate: the <strong>$x</strong> variable is in fact implemented as a MySQL user defined variable 
called <strong>@__qs_local_var_16</strong>. It has the value of <strong>3</strong>. However, the <strong>:$x</strong> value is
the <i>expansion</i> of <strong>$x</strong>, and is <i>the constant <strong>3</strong></i> (as is evident from column's name).
</p>

<p>
Both <strong>:$x</strong> and <strong>:${x}</strong> result with the expanded value of 
<strong>$x</strong>. The latter is a more expressive form, and is useful in resolving ambiguities
as in the following:
<blockquote><pre>
var $table_name := 'links';
CREATE TABLE test.:$table_name;                 -- fine
CREATE TABLE test.personal_:$table_name;        -- fine
CREATE TABLE test.:$table_name_to_categories;   -- impossible to resolve variable name
CREATE TABLE test.:${table_name}_to_categories; -- fine

</pre></blockquote>
</p>

<p>
Now consider cases where variables cannot be used, yet expansion allows for seamless script approach: 
<blockquote><pre>
set @n := 2;
var $x := @n + 1;

-- An error: -- SELECT Name FROM world.City ORDER BY Population DESC LIMIT @n;
-- An error: -- SELECT Name FROM world.City ORDER BY Population DESC LIMIT $x;
-- 
-- A valid statement:
SELECT Name FROM world.City ORDER BY Population DESC LIMIT :$x;

+-----------------+
| Name            |
+-----------------+
| Mumbai (Bombay) |
| Seoul           |
| São Paulo       |
+-----------------+
</pre></blockquote>
</p>

<p>
As another example, consider:
<blockquote><pre>
set @t := 'City';
var $tbl;
set $tbl := 'City';

-- An error: -- ALTER TABLE world.@t ENGINE=InnoDB;
-- An error: -- ALTER TABLE world.$tbl ENGINE=InnoDB;
-- 
-- A valid statement:
ALTER TABLE world.:$tbl ENGINE=InnoDB;
</pre></blockquote>
An <strong>ALTER TABLE</strong> does not accept variables for table names. However, when using <i>expansion</i>, the last statement
translates to <strong>ALTER TABLE world.City ENGINE=InnoDB;</strong> before being sent to MySQL.
</p>

<p>
<i>Expansion</i> occurs just before query execution. It is therefore possible to expand changing values, as follows:
<blockquote><pre>
foreach($t: {City, Country, CountryLanguage})
{
  ALTER TABLE world.:${t} ENGINE=InnoDB;
}
</pre></blockquote>
</p>

<p>
	Expansion limitations:
	<ul>
		<li>Expansion applies for <a href="query_script_statements.html">SQL statements</a>
			and <a href="query_script_expressions.html">expressions</a>.</li>
		<li>Expansion does not apply to variables of QueryScript statements.</li>
		<li>Expansion in expressions only applies once. 
			Thus, in a <strong>while(:${condition}) {...}</strong> loop, the 
			expansion of <strong>:${condition}</strong> occurs at one time only,
			before the first loop iteration. Future changes to the <strong>$condition</strong>
			local variable itself do not affect the expression.</li>
		<li>Expanded variables must not specify local variables. Expanding the variable
			<strong>var $some_var := '$another_var'</strong> will result in a runtime
			error. You may relate to MySQL's user defined variables.</li>
	</ul>
</p>

<a name="builtin"></a>
<h4>Built-in variables</h4>
<p>
	The following variables are built into the system:
	<ul>
		<li>
			<strong>$found_rows</strong>: number of rows returned by previous 
			<strong>SELECT</strong> statement, if any.
			<br/>This value reflects MySQL's 
			<a href="http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows">FOUND_ROWS()</a>.
			Due to the interpreted nature of QueryScript, the transient <i>FOUND_ROWS()</i> value is
			lost by the time next statement executes. Hence the use of this variable.
			<br/>This variable is transient, in that it only relates to the previously
			executed statement. 
		</li>
		<li>
			<strong>$rowcount</strong>: number of rows changed, deleted, or inserted by the last statement,
			if applicable.
			<br/>This value reflects MySQL's 
			<a href="http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_row-count">ROW_COUNT()</a>.
			Due to the interpreted nature of QueryScript, the transient <i>ROW_COUNT()</i> value is
			lost by the time next statement executes. Hence the use of this variable.
			<br/>This variable is transient, in that it only relates to the previously
			executed statement. 
		</li>
	</ul>
</p>

<h3>SEE ALSO</h3>
<a href="query_script_foreach.html">foreach</a>,
<a href="query_script_input.html">input</a>,
<a href="query_script_var.html">var</a>

<h3>AUTHOR</h3>
Shlomi Noach


				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
